package co.gov.medellin.ssti.sub.model.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import co.gov.medellin.ssti.sub.common.excepcion.SUBException;
import co.gov.medellin.ssti.sub.model.dao.IProgramaDAO;
import co.gov.medellin.ssti.sub.model.dao.exception.DAOException;
import co.gov.medellin.ssti.sub.model.vo.ProgramaVO;

public class ProgramaDAOImpl extends BaseDAOImpl implements IProgramaDAO {

	// **************************************
	// QUERYS
	// **************************************

	private static final String SELECT_TODOS_PROGRAMAS_VIGENTES = "SELECT P.CODIGO CODIGO, "
			+ "P.AREA_CODIGO AREA, P.NOMBRE NOMBRE, P.DESCRIPCION DESCRIPCION, "
			+ "P.USU_CODIGO USU_CODIGO, P.VIGENCIA VIGENCIA FROM TCS_BEN_PROGRAMA P "
			+ "WHERE P.VIGENCIA = 'A' ORDER BY NOMBRE ";

	private static final String SELECT_TODOS_PROGRAMAS_BY_AREA = "SELECT P.CODIGO CODIGO, "
			+ "P.AREA_CODIGO AREA, P.NOMBRE NOMBRE, P.DESCRIPCION DESCRIPCION, "
			+ "P.USU_CODIGO USU_CODIGO, P.VIGENCIA VIGENCIA FROM TCS_BEN_PROGRAMA P "
			+ "WHERE P.VIGENCIA = 'A' AND P.AREA_CODIGO = ? ORDER BY NOMBRE ";

	public ProgramaDAOImpl(Connection con) {
		super(con);
	}

	public List<ProgramaVO> loadAllProgramas() throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		List<ProgramaVO> programas = new ArrayList<ProgramaVO>();

		try {
			pstmt = conn.prepareStatement(SELECT_TODOS_PROGRAMAS_VIGENTES);
			rst = pstmt.executeQuery();
			while (rst.next()) {
				ProgramaVO programa = new ProgramaVO();
				programa.setCode(new Long(rst.getLong("CODIGO")));
				programa.setNombre(rst.getString("NOMBRE"));
				programas.add(programa);
			}
		} catch (SQLException e) {
			SUBException.logDebugError("loadAllProgramas: ", e);
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return programas;
	}

	public List<ProgramaVO> loadAllProgramasByArea(String secretaryCode)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		List<ProgramaVO> programas = new ArrayList<ProgramaVO>();

		try {
			pstmt = conn.prepareStatement(SELECT_TODOS_PROGRAMAS_BY_AREA);
			pstmt.setString(1, secretaryCode);
			rst = pstmt.executeQuery();
			while (rst.next()) {
				ProgramaVO programa = new ProgramaVO();
				programa.setCode(new Long(rst.getLong("CODIGO")));
				programa.setNombre(rst.getString("NOMBRE"));
				programas.add(programa);
			}
		} catch (SQLException e) {
			SUBException.logDebugError("loadAllProgramasByArea: ", e);
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return programas;
	}

	public Collection<ProgramaVO> findProgramaByUserReport(Long codeUser,
			String userGroupName, Long codeArea) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		List<Object> parameters = null;
		ProgramaVO criteria = new ProgramaVO();
		String query = "SELECT DISTINCT pro.CODIGO COD_PROGRAMA, pro.NOMBRE NOM_PROGRAMA, pro.DESCRIPCION DESC_PROGRAMA, "
				+ "pro.OBJETIVO, pro.POBLACION_OBJETIVO, area.CODIGO COD_AREA, area.NOMBRE NOM_AREA, "
				+ "usu.CODIGO COD_USUARIO, usu.NOMBRE NOM_USUARIO, a.admProg "
				+ "FROM VCS_BEN_SUBPROG_ALLOWED a, "
				+ "TCS_BEN_PROGRAMA pro, TCS_BEN_AREA area, paoyer.TCS_USUARIO usu, "
				+ "paoyer.tcs_grupo_usuario gus, TCS_BEN_SUBPROGRAMA_USUARIO subus "
				+ "WHERE  gus.CODIGO = usu.GUS_CODIGO "
				+ "AND a.COD_PROGRAMA = pro.CODIGO "
				+ "AND pro.AREA_CODIGO = area.CODIGO "
				+ "AND pro.USU_CODIGO = usu.CODIGO "
				+ "AND pro.VIGENCIA = 'A' ";

		if ((Object) codeUser != null) {
			parameters = new ArrayList<Object>();
			query += "AND ('ADMINISTRADOR GENERAL' = ? "
					+ " OR 'SUPER USUARIO' = ? OR admSubpro = ? "
					+ " OR pro.USU_CODIGO = ? OR a.permisos = ? ";
			parameters.add(userGroupName);
			parameters.add(userGroupName);
			parameters.add(codeUser);
			parameters.add(codeUser);
			parameters.add(codeUser);
			if (userGroupName != null
					&& userGroupName.equals("ADMINISTRADOR SECRETARIA")) {
				query += " OR COD_AREA = ? ";
				parameters.add(codeArea);
			}
			query += " ) ";
		} else {
			return null;
		}

		if (criteria.getCode() != null) {
			query += "AND COD_PROGRAMA = ? ";
			parameters.add(criteria.getCode());
		}
		if (!criteria.getNombre().equalsIgnoreCase("")) {
			query += "AND NOM_PROGRAMA LIKE ? ";
			parameters.add("%" + criteria.getNombre().toUpperCase().trim()
					+ "%");
		}
		if (criteria.getCodeArea() != null) {
			query += "AND COD_AREA = ? ";
			parameters.add(criteria.getCodeArea());
		}

		query += " ORDER BY NOM_PROGRAMA";
		// System.out.println("query: " + query);
		ArrayList<ProgramaVO> programas = new ArrayList<ProgramaVO>();

		try {
			pstmt = conn.prepareStatement(query);
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();

			while (rst.next()) {
				ProgramaVO pro = new ProgramaVO();
				pro.setCode(new Long(rst.getLong("COD_PROGRAMA")));
				pro.setNombre(rst.getString("NOM_PROGRAMA"));
				pro.setDescripcion(rst.getString("DESC_PROGRAMA"));
				pro.setObjetivo(rst.getString("OBJETIVO"));
				pro.setPoblacionObjetivo(rst.getString("POBLACION_OBJETIVO"));
				pro.setCodeArea(new Long(rst.getString("COD_AREA")));
				pro.setNombreArea(rst.getString("NOM_AREA"));
				pro.setCodeUsuario(new Long(rst.getString("COD_USUARIO")));
				pro.setNombreUsuario(rst.getString("NOM_USUARIO"));
				programas.add(pro);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return programas;
	}

	public Collection<ProgramaVO> findProgramaByCriteria(ProgramaVO criteria)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		StringBuffer query = new StringBuffer(
				"SELECT pro.CODIGO COD_PROGRAMA, pro.NOMBRE NOM_PROGRAMA, "
						+ " pro.DESCRIPCION DESC_PROGRAMA, pro.OBJETIVO, "
						+ " pro.POBLACION_OBJETIVO, area.CODIGO COD_AREA, area.NOMBRE NOM_AREA, "
						+ " usu.CODIGO COD_USUARIO, usu.NOMBRE NOM_USUARIO "
						+ " FROM TCS_BEN_PROGRAMA pro, "
						+ " TCS_BEN_AREA area, "
						+ " paoyer.TCS_USUARIO usu "
						+ " WHERE Fcs_Ben_Val_Vigencia(pro.VIGENCIA, pro.DESDE_VIGENCIA, pro.HASTA_VIGENCIA, sysdate) = 'A' "
						+ " AND pro.AREA_CODIGO = area.CODIGO "
						+ " AND pro.USU_CODIGO = usu.CODIGO");

		List<Object> parameters = new ArrayList<Object>();
		if (criteria.getCode() != null) {
			query.append(" AND pro.CODIGO = ? ");
			parameters.add(criteria.getCode());
		}
		if (!criteria.getNombre().equalsIgnoreCase("")) {
			query.append(" AND pro.NOMBRE LIKE ? ");
			parameters.add("%" + criteria.getNombre().toUpperCase().trim()
					+ "%");
		}
		if (criteria.getCodeArea() != null) {
			query.append(" AND area.CODIGO = ");
			parameters.add(criteria.getCodeArea());
		}

		query.append(" ORDER BY pro.NOMBRE");

		ArrayList<ProgramaVO> programas = new ArrayList<ProgramaVO>();
		try {
			pstmt = conn.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();

			ProgramaVO pro = null;
			while (rst.next()) {
				pro = new ProgramaVO();
				pro.setCode(new Long(rst.getLong("COD_PROGRAMA")));
				pro.setNombre(rst.getString("NOM_PROGRAMA"));
				pro.setDescripcion(rst.getString("DESC_PROGRAMA"));
				pro.setObjetivo(rst.getString("OBJETIVO"));
				pro.setPoblacionObjetivo(rst.getString("POBLACION_OBJETIVO"));
				pro.setCodeArea(new Long(rst.getString("COD_AREA")));
				pro.setNombreArea(rst.getString("NOM_AREA"));
				pro.setCodeUsuario(new Long(rst.getString("COD_USUARIO")));
				pro.setNombreUsuario(rst.getString("NOM_USUARIO"));
				programas.add(pro);
				pro = null;
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return programas;
	}

	public Collection<ProgramaVO> findAllProgramaByCriteria(ProgramaVO criteria)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		StringBuffer query = new StringBuffer(
				"SELECT pro.CODIGO COD_PROGRAMA, pro.NOMBRE NOM_PROGRAMA, "
						+ " pro.DESCRIPCION DESC_PROGRAMA, pro.OBJETIVO, "
						+ " pro.POBLACION_OBJETIVO, area.CODIGO COD_AREA, area.NOMBRE NOM_AREA, "
						+ " usu.CODIGO COD_USUARIO, usu.NOMBRE NOM_USUARIO "
						+ " FROM TCS_BEN_PROGRAMA pro, "
						+ " TCS_BEN_AREA area, " + " paoyer.TCS_USUARIO usu "
						+ " WHERE pro.AREA_CODIGO = area.CODIGO "
						+ " AND pro.USU_CODIGO = usu.CODIGO");

		List<Object> parameters = new ArrayList<Object>();

		if (criteria.getCode() != null) {
			query.append(" AND pro.CODIGO = ? ");
			parameters.add(criteria.getCode());
		}
		if (!criteria.getNombre().equalsIgnoreCase("")) {
			query.append(" AND pro.NOMBRE LIKE ? ");
			parameters.add("%" + criteria.getNombre().toUpperCase().trim()
					+ "%");
		}
		if (criteria.getCodeArea() != null) {
			query.append(" AND area.CODIGO = ? ");
			parameters.add(criteria.getCodeArea());
		}

		query.append(" ORDER BY pro.NOMBRE");

		ArrayList<ProgramaVO> programas = new ArrayList<ProgramaVO>();
		try {
			pstmt = conn.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();

			while (rst.next()) {
				ProgramaVO pro = new ProgramaVO();
				pro.setCode(new Long(rst.getLong("COD_PROGRAMA")));
				pro.setNombre(rst.getString("NOM_PROGRAMA"));
				pro.setDescripcion(rst.getString("DESC_PROGRAMA"));
				pro.setObjetivo(rst.getString("OBJETIVO"));
				pro.setPoblacionObjetivo(rst.getString("POBLACION_OBJETIVO"));
				pro.setCodeArea(new Long(rst.getString("COD_AREA")));
				pro.setNombreArea(rst.getString("NOM_AREA"));
				pro.setCodeUsuario(new Long(rst.getString("COD_USUARIO")));
				pro.setNombreUsuario(rst.getString("NOM_USUARIO"));
				programas.add(pro);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return programas;
	}

	public Collection<ProgramaVO> findProgramaByCriteria(ProgramaVO criteria,
			Long codeUser, String groupUserName, Long areaUser)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		StringBuffer query = new StringBuffer(
				"SELECT DISTINCT COD_PROGRAMA, NOM_PROGRAMA, DESC_PROGRAMA, OBJETIVO, POBLACION_OBJETIVO, COD_AREA, NOM_AREA, COD_USUARIO, NOM_USUARIO  from VCS_BEN_PROGRAMAS  ");
		List<Object> parameters = new ArrayList<Object>();

		if ((Object) codeUser != null) {
			query.append(" WHERE ('ADMINISTRADOR GENERAL' = ? "
					+ " OR 'SUPER USUARIO' = ? OR admSubpro = ? "
					+ "          OR admProg = ? ");
			parameters.add(groupUserName);
			parameters.add(groupUserName);
			parameters.add(codeUser);
			parameters.add(codeUser);

			if (groupUserName != null
					&& groupUserName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" OR COD_AREA = ? ");
				parameters.add(areaUser);
			}
			query.append(" ) ");
		} else {
			return null;
		}

		if (criteria.getCode() != null) {
			query.append(" AND COD_PROGRAMA = ? ");
			parameters.add(criteria.getCode());
		}
		if (!criteria.getNombre().equalsIgnoreCase("")) {
			query.append(" AND NOM_PROGRAMA LIKE ? ");
			parameters.add("%" + criteria.getNombre().toUpperCase().trim()
					+ "%");
		}
		if (criteria.getCodeArea() != null) {
			query.append(" AND COD_AREA = ? ");
			parameters.add(criteria.getCodeArea());
		}

		query.append(" ORDER BY NOM_PROGRAMA");

		ArrayList<ProgramaVO> programas = new ArrayList<ProgramaVO>();

		try {
			pstmt = conn.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();

			while (rst.next()) {
				ProgramaVO pro = new ProgramaVO();
				pro.setCode(new Long(rst.getLong("COD_PROGRAMA")));
				pro.setNombre(rst.getString("NOM_PROGRAMA"));
				pro.setDescripcion(rst.getString("DESC_PROGRAMA"));
				pro.setObjetivo(rst.getString("OBJETIVO"));
				pro.setPoblacionObjetivo(rst.getString("POBLACION_OBJETIVO"));
				pro.setCodeArea(new Long(rst.getString("COD_AREA")));
				pro.setNombreArea(rst.getString("NOM_AREA"));
				pro.setCodeUsuario(new Long(rst.getString("COD_USUARIO")));
				pro.setNombreUsuario(rst.getString("NOM_USUARIO"));
				programas.add(pro);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return programas;
	}

	public Collection<ProgramaVO> findProgramaByCriteriaOB(ProgramaVO criteria,
			Long codeUser, String groupUserName, Long areaUser)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		StringBuffer query = new StringBuffer(
				"SELECT DISTINCT COD_PROGRAMA, NOM_PROGRAMA, DESC_PROGRAMA, OBJETIVO, POBLACION_OBJETIVO, COD_AREA, NOM_AREA, COD_USUARIO, NOM_USUARIO  from VCS_BEN_PROGRAMAS ");
		List<Object> parameters = new ArrayList<Object>();

		if ((Object) codeUser != null) {
			query.append(" WHERE ('ADMINISTRADOR GENERAL' = ? "
					+ " OR 'SUPER USUARIO' = ? OR admSubpro = ? "
					+ "          OR admProg = ? OR ususede = ? ");
			parameters.add(groupUserName);
			parameters.add(groupUserName);
			parameters.add(codeUser);
			parameters.add(codeUser);
			parameters.add(codeUser);

			if (groupUserName != null
					&& groupUserName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" OR COD_AREA = ? ");
				parameters.add(areaUser);
			}
			query.append(" ) ");
		} else {
			return null;
		}

		if (criteria.getCode() != null) {
			query.append(" AND COD_PROGRAMA = ? ");
			parameters.add(criteria.getCode());
		}
		if (!criteria.getNombre().equalsIgnoreCase("")) {
			query.append(" AND NOM_PROGRAMA LIKE ? ");
			parameters.add("%" + criteria.getNombre().toUpperCase().trim()
					+ "%");
		}
		if (criteria.getCodeArea() != null) {
			query.append(" AND COD_AREA = ? ");
			parameters.add(criteria.getCodeArea());
		}

		query.append(" ORDER BY NOM_PROGRAMA");

		ArrayList<ProgramaVO> programas = new ArrayList<ProgramaVO>();
		try {
			pstmt = conn.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();

			while (rst.next()) {
				ProgramaVO pro = new ProgramaVO();
				pro.setCode(new Long(rst.getLong("COD_PROGRAMA")));
				pro.setNombre(rst.getString("NOM_PROGRAMA"));
				pro.setDescripcion(rst.getString("DESC_PROGRAMA"));
				pro.setObjetivo(rst.getString("OBJETIVO"));
				pro.setPoblacionObjetivo(rst.getString("POBLACION_OBJETIVO"));
				pro.setCodeArea(new Long(rst.getString("COD_AREA")));
				pro.setNombreArea(rst.getString("NOM_AREA"));
				pro.setCodeUsuario(new Long(rst.getString("COD_USUARIO")));
				pro.setNombreUsuario(rst.getString("NOM_USUARIO"));
				programas.add(pro);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return programas;
	}

	public Collection<ProgramaVO> findProgramaWithoutSede(ProgramaVO criteria,
			Long codeUser, String groupUserName, Long areaUser)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		StringBuffer query = new StringBuffer(
				"SELECT DISTINCT pro.CODIGO COD_PROGRAMA, pro.NOMBRE NOM_PROGRAMA, "
						+ " pro.DESCRIPCION DESC_PROGRAMA, pro.OBJETIVO, pro.POBLACION_OBJETIVO, "
						+ " area.CODIGO COD_AREA, area.NOMBRE NOM_AREA,	usu.CODIGO COD_USUARIO, "
						+ " usu.NOMBRE NOM_USUARIO, a.admProg "
						+ " FROM VCS_BEN_SUBPROG_WITHOUT_SEDE a, TCS_BEN_PROGRAMA pro, "
						+ " TCS_BEN_AREA area, paoyer.TCS_USUARIO usu, paoyer.tcs_grupo_usuario gus "
						+ " WHERE  gus.CODIGO = usu.GUS_CODIGO "
						+ " AND a.COD_PROGRAMA = pro.CODIGO "
						+ " AND Fcs_Ben_Val_Vigencia(pro.VIGENCIA, pro.DESDE_VIGENCIA, pro.HASTA_VIGENCIA, SYSDATE) = 'A' "
						+ " AND pro.AREA_CODIGO = area.CODIGO "
						+ " AND pro.USU_CODIGO = usu.CODIGO ");

		List<Object> parameters = new ArrayList<Object>();
		if ((Object) codeUser != null) {
			query.append(" AND ('ADMINISTRADOR GENERAL' = ? "
					+ " OR 'SUPER USUARIO' = ? OR admSubpro = ? "
					+ "          OR pro.USU_CODIGO = ? ");
			parameters.add(groupUserName);
			parameters.add(groupUserName);
			parameters.add(codeUser);
			parameters.add(codeUser);

			if (groupUserName != null
					&& groupUserName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" OR area.Codigo = ? ");
				parameters.add(areaUser);
			}
			query.append(" ) ");
		} else {
			return null;
		}

		if (criteria.getCode() != null) {
			query.append("AND COD_PROGRAMA = ? ");
			parameters.add(criteria.getCode());
		}
		if (!criteria.getNombre().equalsIgnoreCase("")) {
			query.append("AND NOM_PROGRAMA LIKE ? ");
			parameters.add("%" + criteria.getNombre().toUpperCase().trim()
					+ "%");
		}
		if (criteria.getCodeArea() != null) {
			query.append("AND COD_AREA = ? ");
			parameters.add(criteria.getCodeArea());
		}

		query.append(" ORDER BY NOM_PROGRAMA");

		ArrayList<ProgramaVO> programas = new ArrayList<ProgramaVO>();
		try {
			pstmt = conn.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();

			while (rst.next()) {
				ProgramaVO pro = new ProgramaVO();
				pro.setCode(new Long(rst.getLong("COD_PROGRAMA")));
				pro.setNombre(rst.getString("NOM_PROGRAMA"));
				pro.setDescripcion(rst.getString("DESC_PROGRAMA"));
				pro.setObjetivo(rst.getString("OBJETIVO"));
				pro.setPoblacionObjetivo(rst.getString("POBLACION_OBJETIVO"));
				pro.setCodeArea(new Long(rst.getString("COD_AREA")));
				pro.setNombreArea(rst.getString("NOM_AREA"));
				pro.setCodeUsuario(new Long(rst.getString("COD_USUARIO")));
				pro.setNombreUsuario(rst.getString("NOM_USUARIO"));
				programas.add(pro);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return programas;
	}

	public Collection<ProgramaVO> findProgramaByCriteria(ProgramaVO criteria,
			String groupUserName, Long codeUser, Long areaUser)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		ArrayList<ProgramaVO> programas = new ArrayList<ProgramaVO>();
		List<Object> parameters = new ArrayList<Object>();

		StringBuffer query = new StringBuffer(
				"SELECT  upper(NOM_PROGRAMA) Programa, COD_PROGRAMA Codigo, "
						+ "COD_AREA codArea, NOM_AREA nombreArea "
						+ " from VCS_BEN_SOLO_PROGRAMAS_EST_VIG ");

		if (!groupUserName.equalsIgnoreCase("") && codeUser != null) {
			query.append(" where ('ADMINISTRADOR GENERAL' = ? "
					+ " OR 'SUPER USUARIO' = ? "
					+ "          OR COD_USUARIO = ? ");
			parameters.add(groupUserName);
			parameters.add(groupUserName);
			parameters.add(codeUser);

			if (groupUserName != null
					&& groupUserName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" or COD_AREA = ? ");
				parameters.add(areaUser);
			}
			query.append(" ) ");
		}
		if (!criteria.getNombre().equalsIgnoreCase("")) {
			query.append(" AND NOM_PROGRAMA LIKE ? ");
			parameters.add("%" + criteria.getNombre().toUpperCase().trim()
					+ "%");
		}

		if (criteria.getCodePrograma() != null
				&& !criteria.getCodePrograma().equalsIgnoreCase("")) {
			query.append(" AND COD_PROGRAMA = ? ");
			parameters.add(criteria.getCodePrograma().trim());
		}

		if (criteria.getCodeArea() != null) {
			query.append(" AND COD_AREA = ? ");
			parameters.add(criteria.getCodeArea());
		}

		if (criteria.getVigencia() != null) {
			query.append(" AND (vigencia = ? OR sysdate > HASTA_VIGENCIA) ");
			parameters.add(criteria.getVigencia());
		}

		query.append(" ORDER BY NOM_PROGRAMA");

		try {
			pstmt = conn.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();

			while (rst.next()) {
				ProgramaVO pro = new ProgramaVO();
				pro.setCode(new Long(rst.getLong("CODIGO")));
				pro.setNombre(rst.getString("PROGRAMA"));
				pro.setCodeArea(new Long(rst.getString("CODAREA")));
				pro.setNombreArea(rst.getString("NombreAREA"));
				programas.add(pro);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}

		return programas;
	}

	public Collection<ProgramaVO> findProgramaByCriteriaReport(
			ProgramaVO criteria, String groupUserName, Long codeUser,
			Long areaUser) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		ArrayList<ProgramaVO> programas = new ArrayList<ProgramaVO>();
		StringBuffer query = new StringBuffer(
				"SELECT  UPPER(NOM_PROGRAMA) Programa, COD_PROGRAMA Codigo, "
						+ " COD_AREA codArea, NOM_AREA nombreArea "
						+ " FROM VCS_BEN_SOLO_PROGRAMAS_EST_VIG v ");
		List<Object> parameters = new ArrayList<Object>();

		if (!groupUserName.equalsIgnoreCase("") && codeUser != null) {
			query.append(" WHERE ('ADMINISTRADOR GENERAL' = ? "
					+ " OR 'SUPER USUARIO' = ? "
					+ " OR COD_PROGRAMA=any(SELECT pro.codigo "
					+ " FROM TCS_BEN_PROGRAMA pro "
					+ " WHERE pro.usu_codigo = ? "
					+ " UNION SELECT sub.prg_codigo "
					+ " FROM tcs_ben_subprograma sub "
					+ " WHERE sub.usu_codigo = ? "
					+ " UNION SELECT sub.prg_codigo "
					+ " FROM tcs_ben_subprograma sub, tcs_ben_subprograma_usuario subus "
					+ " WHERE sub.CODIGO = subus.SBPR_CODIGO "
					+ " AND subus.usu_codigo = ? )");
			parameters.add(groupUserName);
			parameters.add(groupUserName);
			parameters.add(codeUser);
			parameters.add(codeUser);
			parameters.add(codeUser);

			if (groupUserName != null
					&& groupUserName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" OR COD_AREA = ? ");
				parameters.add(areaUser);
			}
			query.append(" ) ");
		}
		/*
		 * if(!criterea.getNombre().equalsIgnoreCase("")) { query +=
		 * " AND NOM_PROGRAMA LIKE '%"
		 * +criterea.getNombre().toUpperCase().trim()+"%' "; }
		 */

		/*
		 * if(criterea.getCodePrograma() != null &&
		 * !criterea.getCodePrograma().equalsIgnoreCase("")) { query +=
		 * " AND COD_PROGRAMA = "+criterea.getCodePrograma().trim()+" "; }
		 */

		if (criteria.getCodeArea() != null) {
			query.append(" AND COD_AREA = ? ");
			parameters.add(criteria.getCodeArea());
		}

		if (criteria.getVigencia() != null) {
			query.append(" AND (vigencia = ? "
					+ " OR sysdate > HASTA_VIGENCIA) ");
			parameters.add(criteria.getVigencia());
		}

		query.append(" ORDER BY NOM_PROGRAMA");
//		System.out.println(query.toString());
//		for (Object s : parameters) {
//			System.out.println(s.toString());
//		}
		try {
			pstmt = conn.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();

			while (rst.next()) {
				ProgramaVO pro = new ProgramaVO();
				pro.setCode(new Long(rst.getLong("CODIGO")));
				pro.setNombre(rst.getString("PROGRAMA"));
				pro.setCodeArea(new Long(rst.getString("CODAREA")));
				pro.setNombreArea(rst.getString("NombreAREA"));
				programas.add(pro);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return programas;
	}

	public Collection<ProgramaVO> findProgramaWithoutVigencia(
			String groupUserName, Long codeUser, Long areaUser)
			throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		ArrayList<ProgramaVO> programas = new ArrayList<ProgramaVO>();
		List<Object> parameters = new ArrayList<Object>();

		StringBuffer query = new StringBuffer(
				"SELECT DISTINCT UPPER(NOM_PROGRAMA) Programa, COD_PROGRAMA Codigo, "
						+ " COD_AREA codArea, NOM_AREA nombreArea "
						+ " FROM VCS_BEN_SOLO_PROGRAMAS ");

		if (!groupUserName.equalsIgnoreCase("") && codeUser != null) {
			query.append(" WHERE ('ADMINISTRADOR GENERAL' = ? "
					+ " OR 'SUPER USUARIO' = ? "
					+ "          OR COD_USUARIO = ? ");
			parameters.add(groupUserName);
			parameters.add(groupUserName);
			parameters.add(codeUser);

			if (groupUserName != null
					&& groupUserName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" OR COD_AREA = ? ");
				parameters.add(areaUser);
			}
			query.append(" ) ");
		}
		query.append(" AND (vigencia = 'I' OR sysdate > HASTA_VIGENCIA) ORDER BY Programa");

		try {
			pstmt = conn.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();

			while (rst.next()) {
				ProgramaVO pro = new ProgramaVO();
				pro.setCode(new Long(rst.getLong("CODIGO")));
				pro.setNombre(rst.getString("PROGRAMA"));
				pro.setCodeArea(new Long(rst.getString("CODAREA")));
				pro.setNombreArea(rst.getString("NombreAREA"));
				programas.add(pro);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return programas;
	}

	public boolean EstadoPrograma(ProgramaVO criteria) {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = "SELECT  codigo FROM tcs_ben_programa where  vigencia = 'I' AND codigo = ? ";

		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, criteria.getCodePrograma());
			rst = pstmt.executeQuery();
			return rst.next();
		} catch (SQLException e) {
			System.err.println("/******** TDSUB - cwsub");
			e.printStackTrace();
			System.err.println("TDSUB - cwsub ********/");
			return false;
		} finally {
			close(rst, pstmt);
		}
	}

	public int getActivarProg(ProgramaVO criteria) {
		PreparedStatement pstmt = null;

		String strSql = " UPDATE tcs_ben_programa SET vigencia = 'A' , comentario = '', hasta_vigencia=hasta_vigencia+TO_NUMBER(hasta_vigencia-desde_vigencia) "
				+ " WHERE codigo = ? ";
		int modificar = 0;

		try {
			pstmt = conn.prepareStatement(strSql);
			pstmt.setString(1, criteria.getCodePrograma());
			return pstmt.executeUpdate();
		} catch (Exception e) {
			System.err.println("/******** TDSUB - cwsub");
			e.printStackTrace();
			System.err.println("TDSUB - cwsub ********/");
		} finally {
			close(pstmt);
		}
		return modificar;
	}

	public Collection<ProgramaVO> getProgramasVigentes(ProgramaVO criteria,
			String groupUserName, Long codeUser, Long areaUser,
			boolean controlAcceso) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		ArrayList<ProgramaVO> programas = new ArrayList<ProgramaVO>();
		List<Object> parameters = new ArrayList<Object>();

		StringBuffer query = new StringBuffer(
				"SELECT  UPPER(NOM_PROGRAMA) Programa, COD_PROGRAMA Codigo, "
						+ " COD_AREA codArea, NOM_AREA nombreArea "
						+ " FROM VCS_BEN_SOLO_PROGRAMAS_EST_VIG ");

		if (!groupUserName.equalsIgnoreCase("") && codeUser != null) {
			query.append(" WHERE ('ADMINISTRADOR GENERAL' = ? OR 'SUPER USUARIO' = ? "
					+ "          OR COD_USUARIO = ? ");
			parameters.add(groupUserName);
			parameters.add(groupUserName);
			parameters.add(codeUser);

			if (groupUserName != null
					&& groupUserName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" OR COD_AREA = ? ");
				parameters.add(areaUser);
			}
			query.append(" ) ");
		}

		if (criteria != null) {
			if (criteria.getCodePrograma() != null
					&& !criteria.getCodePrograma().equalsIgnoreCase("")) {
				query.append(" AND COD_PROGRAMA = ? ");
				parameters.add(criteria.getCodePrograma().trim());
			}
		}

		if (controlAcceso && areaUser != null && areaUser.longValue() != 0) {
			query.append(" AND COD_AREA = ? ");
			parameters.add(areaUser.longValue());
		}
		query.append(" ORDER BY NOM_PROGRAMA");

		try {
			pstmt = conn.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();

			while (rst.next()) {
				ProgramaVO pro = new ProgramaVO();
				pro.setCodePrograma(rst.getString("CODIGO"));
				pro.setNombre(rst.getString("PROGRAMA"));
				pro.setCodeArea(new Long(rst.getString("CODAREA")));
				pro.setNombreArea(rst.getString("NombreAREA"));
				programas.add(pro);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return programas;
	}

	public String getNameByCode(String strCodeProg) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		String query = "SELECT nombre FROM tcs_ben_programa "
				+ " WHERE codigo = ? ";

		String strName = null;
		try {
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, strCodeProg);
			rst = pstmt.executeQuery();

			if (rst.next()) {
				strName = rst.getString("nombre");
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return strName;
	}

	public Collection<ProgramaVO> findProgramaByCriteriaWithGestor(
			ProgramaVO criteria, String groupUserName, Long codeUser,
			Long areaUser) throws DAOException {
		PreparedStatement pstmt = null;
		ResultSet rst = null;

		ArrayList<ProgramaVO> programas = new ArrayList<ProgramaVO>();
		List<Object> parameters = new ArrayList<Object>();

		StringBuffer query = new StringBuffer(
				"SELECT DISTINCT NOM_PROGRAMA Programa, COD_PROGRAMA Codigo, "
						+ " COD_AREA codArea, NOM_AREA nombreArea "
						+ " FROM VCS_BEN_SOLO_PROGRAMAS_EST_VIG, TCS_BEN_SUBPROGRAMA SUB, TCS_BEN_SUBP_ATR_BENEFICIARIO SAB ");

		if (!groupUserName.equalsIgnoreCase("") && codeUser != null) {
			query.append(" WHERE ('ADMINISTRADOR GENERAL' = ? "
					+ " OR 'SUPER USUARIO' = ? "
					+ "          OR COD_USUARIO = ? ");
			parameters.add(groupUserName);
			parameters.add(groupUserName);
			parameters.add(codeUser);

			if (groupUserName != null
					&& groupUserName.equals("ADMINISTRADOR SECRETARIA")) {
				query.append(" OR COD_AREA = ? ");
				parameters.add(areaUser);
			}
			query.append(" ) ");
		}
		if (!criteria.getNombre().equalsIgnoreCase("")) {
			query.append(" AND NOM_PROGRAMA LIKE ? ");
			parameters.add("%" + criteria.getNombre().toUpperCase().trim()
					+ "%");
		}

		if (criteria.getCodePrograma() != null
				&& !criteria.getCodePrograma().equalsIgnoreCase("")) {
			query.append(" AND COD_PROGRAMA = ? ");
			parameters.add(criteria.getCodePrograma().trim());
		}

		if (criteria.getCodeArea() != null) {
			query.append(" AND COD_AREA = ? ");
			parameters.add(criteria.getCodeArea());
		}

		if (criteria.getVigencia() != null) {
			query.append(" AND (vigencia = ? "
					+ " OR sysdate > HASTA_VIGENCIA) ");
			parameters.add(criteria.getVigencia());
		}

		query.append(" AND SUB.prg_codigo = COD_PROGRAMA AND SAB.SBPR_CODIGO = SUB.CODIGO AND UPPER(TRIM(SAB.NOMBRE)) = 'GESTOR' ");
		query.append(" ORDER BY NOM_PROGRAMA");

		try {
			pstmt = conn.prepareStatement(query.toString());
			pstmt = setQueryParameters(pstmt, parameters);
			rst = pstmt.executeQuery();

			while (rst.next()) {
				ProgramaVO pro = new ProgramaVO();
				pro.setCode(new Long(rst.getLong("CODIGO")));
				pro.setNombre(rst.getString("PROGRAMA").toUpperCase());
				pro.setCodeArea(new Long(rst.getString("CODAREA")));
				pro.setNombreArea(rst.getString("NombreAREA"));
				programas.add(pro);
			}
		} catch (SQLException e) {
			throw new DAOException(e);
		} finally {
			close(rst, pstmt);
		}
		return programas;
	}
}
